package com.kanche.bigbaby.collection;

import android.content.Context;
import android.database.Cursor;

import com.kanche.bigbaby.R;
import com.kanche.bigbaby.collection.domain.Gallery;
import com.kanche.bigbaby.collection.domain.Photo;
import com.kanche.bigbaby.collection.domain.Vehicle;
import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

//import com.kkche.merchant.R;
//import com.kkche.merchant.domain.Gallery;
//import com.kkche.merchant.domain.Photo;
//import com.kkche.merchant.domain.Vehicle;
//import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

/**
 * Created by nealmi on 6/12/14.
 */
public class KKCheDBHelper extends SQLiteAssetHelper {
    private static final String DATABASE_NAME = "bss_vehicle_collect.sqlite3";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_VEHICLES = "vehicles";
    private final Context context;

    public KKCheDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    /**
     * 注意:请仅在退出登录时调用
     *
     * @param userId
     */
//    public void logout(String userId) {
//        String tableName = TABLE_VEHICLES + "_" + userId;
//        String backupSql = context.getString(R.string.sql_backup_vehicles_table);
//        backupSql = String.format(backupSql, tableName);
//        getWritableDatabase().beginTransaction();
//        getWritableDatabase().execSQL(backupSql);
//        getWritableDatabase().setTransactionSuccessful();
//        getWritableDatabase().endTransaction();
//
//    }

    public void dropTable(String tableName) {
        String dropSql = context.getString(R.string.sql_drop_table);
        dropSql = String.format(dropSql, tableName);
        getWritableDatabase().beginTransaction();
        getWritableDatabase().execSQL(dropSql);
        getWritableDatabase().setTransactionSuccessful();
        getWritableDatabase().endTransaction();
    }

    /**
     * 注意:请仅在登录成功时调用
     *
     * @param userId
     */
//    public void login(String userId) {
//        String tableName = TABLE_VEHICLES + "_" + userId;
//
//        if (isTableExists(tableName)) {//如果该用户有数据, 恢复数据
//            if (isTableExists(TABLE_VEHICLES)) {
//                dropTable(TABLE_VEHICLES);
//            }
//            String restoreSql = context.getString(R.string.sql_restore_vehicles_table);
//            restoreSql = String.format(restoreSql, tableName);
//            getWritableDatabase().beginTransaction();
//            getWritableDatabase().execSQL(restoreSql);
//            getWritableDatabase().setTransactionSuccessful();
//            getWritableDatabase().endTransaction();
//        } else {//如果该用户无数据且不存在vehicles表,创建vehicles表
//            if (!isTableExists(TABLE_VEHICLES)) {
//                createTableVehicles();
//            }
//        }
//    }

    private void createTableVehicles() {
        String createSql = context.getString(R.string.sql_create_vehicles_table);
        getWritableDatabase().beginTransaction();
        getWritableDatabase().execSQL(createSql);
        getWritableDatabase().setTransactionSuccessful();
        getWritableDatabase().endTransaction();
    }

    private boolean isTableExists(String tableName) {
        String checkSql = context.getString(R.string.sql_check_exist_of_the_table);
        checkSql = String.format(checkSql, tableName);
        Cursor cursor = getReadableDatabase().rawQuery(checkSql, null);
        if (cursor.moveToNext() && cursor.getInt(0) == 1) {
            return true;
        }
        return false;
    }


    public long insertVehicle(Vehicle vehicle) {
        long rowId = getWritableDatabase().insert(TABLE_VEHICLES, null, vehicle.toContentValues());
        return rowId;
    }

    public List<Vehicle> findAllLocalVehicles() {
        if (!isTableExists(TABLE_VEHICLES)) {
            createTableVehicles();
        }
        List<Vehicle> vehicles = new ArrayList<Vehicle>();
        Cursor cursor = getReadableDatabase().query(TABLE_VEHICLES, VehicleColumns._all, VehicleColumns.id + " is null order by " + VehicleColumns.updateAt + " desc", null, null, null, null);
        while (cursor.moveToNext()) {
            vehicles.add(extractVehicleFromCursor(cursor));
        }
        return vehicles;
    }

    public int removeAllVehicles() {
        return getWritableDatabase().delete(TABLE_VEHICLES, null, null);
    }

    public int removeVehicle(long rowId) {
        try {
            Vehicle vehicle = findVehicleByRowId(rowId);
            List<Photo> photos = vehicle.getGallery().getPhotos();
            for (Photo photo : photos) {
                try {
                    if (!StringUtils.isHttpPath(photo.getUri())) {
                        new File(photo.getUri()).delete();
                    }
                } catch (Exception e) {
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return getWritableDatabase().delete(TABLE_VEHICLES, VehicleColumns.rowId + "=?", new String[]{String.valueOf(rowId)});
    }

    public int removeRejectVehicle() {
        return getWritableDatabase().delete(TABLE_VEHICLES, VehicleColumns.reject + "=?", new String[]{String.valueOf(1)});
    }

    public List<Vehicle> findRejectVehicle() {
        if (!isTableExists(TABLE_VEHICLES)) {
            createTableVehicles();
        }
        List<Vehicle> vehicles = new ArrayList<Vehicle>();
        Cursor cursor = getReadableDatabase().query(TABLE_VEHICLES, VehicleColumns._all, VehicleColumns.reject + "=?", new String[]{String.valueOf(1)}, null, null, null, null);
        if (cursor.moveToNext()) {
            vehicles.add(extractVehicleFromCursor(cursor));
        }
        return vehicles;
    }

    public int updateVehicle(long rowId, Vehicle vehicle) {
        vehicle.setUpdateAt(DateUtils.now());
        return getWritableDatabase().update(TABLE_VEHICLES, vehicle.toContentValues(), VehicleColumns.rowId + "=?", new String[]{String.valueOf(rowId)});
    }

    public Vehicle findVehicleByRowId(long rowId) {
        Vehicle vehicle = null;
        Cursor cursor = getReadableDatabase().query(TABLE_VEHICLES, VehicleColumns._all, VehicleColumns.rowId + "=?", new String[]{String.valueOf(rowId)}, null, null, null, null);
        if (cursor.moveToNext()) {
            vehicle = extractVehicleFromCursor(cursor);
        }
        return vehicle;
    }

    private String extractStringValueForColumn(Cursor cursor, String columnName) {
        int index = cursor.getColumnIndex(columnName);
        if (index != -1) {
            return cursor.getString(index);
        }
        return "";
    }

    private long extractLongValueForColumn(Cursor cursor, String columnName) {
        int index = cursor.getColumnIndex(columnName);
        if (index != -1) {
            return cursor.getLong(index);
        }
        return 0L;
    }

    private int extractIntegerValueForColumn(Cursor cursor, String columnName) {
        int index = cursor.getColumnIndex(columnName);
        if (index != -1) {
            return cursor.getInt(index);
        }
        return 0;
    }

    private boolean extractBooleanValueForColumn(Cursor cursor, String columnName) {
        int index = cursor.getColumnIndex(columnName);
        if (index == -1) return false;
        return StringUtils.intToBool(cursor.getInt(index));
    }

    private float extractFloatValueForColumn(Cursor cursor, String columnName) {
        int index = cursor.getColumnIndex(columnName);
        if (index != -1) {
            return cursor.getFloat(index);
        }
        return 0f;
    }

    private Vehicle extractVehicleFromCursor(Cursor cursor) {
        Vehicle vehicle = new Vehicle();
        int index;
        vehicle.set_id(extractLongValueForColumn(cursor, VehicleColumns.rowId));

        vehicle.setId(extractStringValueForColumn(cursor, VehicleColumns.id));
        vehicle.setMerchantId(extractStringValueForColumn(cursor, VehicleColumns.merchantId));
        vehicle.setMerchantUserId(extractStringValueForColumn(cursor, VehicleColumns.merchantUserId));

        vehicle.getSummary().setAccident(extractBooleanValueForColumn(cursor, VehicleColumns.accident));

        vehicle.getVehicleDate().setCompulsoryInsuranceExpireDate(extractStringValueForColumn(cursor, VehicleColumns.alcInsuranceExpireDate));

        vehicle.getPrice().setBidPrice(extractIntegerValueForColumn(cursor, VehicleColumns.bidPrice));

        vehicle.getSpec().setBrand(extractStringValueForColumn(cursor, VehicleColumns.brand));

        vehicle.getDesc().setBrief(extractStringValueForColumn(cursor, VehicleColumns.briefDesc));

        vehicle.getDocument().setCarTax(extractBooleanValueForColumn(cursor, VehicleColumns.carTaxDoc));

        vehicle.getAddress().setCityCode(extractStringValueForColumn(cursor, VehicleColumns.cityCode));
        vehicle.getSummary().setColor(extractStringValueForColumn(cursor, VehicleColumns.color));
        vehicle.getVehicleDate().setCommercialInsuranceExpireDate(extractStringValueForColumn(cursor, VehicleColumns.commercialInsuranceExpireDate));
        vehicle.setContactsJson(extractStringValueForColumn(cursor, VehicleColumns.contacts));

        vehicle.getAddress().setDetail(extractStringValueForColumn(cursor, VehicleColumns.detail));
        vehicle.getAddress().setDistrictCode(extractStringValueForColumn(cursor, VehicleColumns.districtCode));

        vehicle.getStatus().setDraft(extractBooleanValueForColumn(cursor, VehicleColumns.draft));

        vehicle.getSummary().setEmissionStandard(extractStringValueForColumn(cursor, VehicleColumns.emissionStandard));
        vehicle.getDesc().setDetail(extractStringValueForColumn(cursor, VehicleColumns.fullDesc));

        vehicle.setGallery(Gallery.fromJson(extractStringValueForColumn(cursor, VehicleColumns.gallery)));
        vehicle.setGalleryId(extractStringValueForColumn(cursor, VehicleColumns.galleryId));
        vehicle.getVehicleDate().setInspectionDate(extractStringValueForColumn(cursor, VehicleColumns.inspectionDate));

        vehicle.getDocument().setInstructions(extractBooleanValueForColumn(cursor, VehicleColumns.instructionsDoc));

        vehicle.getLocation().setLat(extractFloatValueForColumn(cursor, VehicleColumns.lat));
        vehicle.getLocation().setLng(extractFloatValueForColumn(cursor, VehicleColumns.lng));


        vehicle.getSpec().setLevelId(extractStringValueForColumn(cursor, VehicleColumns.levelId));
        vehicle.getSummary().setLicenseNumber(extractStringValueForColumn(cursor, VehicleColumns.licenseNumber));


        vehicle.getSummary().setMaintenance(extractBooleanValueForColumn(cursor, VehicleColumns.maintenance));
        vehicle.getDocument().setMaintenanceManual(extractBooleanValueForColumn(cursor, VehicleColumns.maintenanceManualDoc));

        vehicle.getSummary().setMileage(extractIntegerValueForColumn(cursor, VehicleColumns.mileage));

        vehicle.getPrice().setOfferPrice(extractIntegerValueForColumn(cursor, VehicleColumns.offerPrice));

        vehicle.getStatus().setOffline(extractBooleanValueForColumn(cursor, VehicleColumns.offline));
        vehicle.getDocument().setProperty(extractBooleanValueForColumn(cursor, VehicleColumns.propertyDoc));

        vehicle.getAddress().setProvinceCode(extractStringValueForColumn(cursor, VehicleColumns.provinceCode));

        vehicle.getDocument().setPurchaseTax(extractBooleanValueForColumn(cursor, VehicleColumns.purchaseTaxDoc));

        vehicle.getSummary().setPurpose(extractStringValueForColumn(cursor, VehicleColumns.purpose));

        vehicle.getPrice().setQuotedPrice(extractIntegerValueForColumn(cursor, VehicleColumns.quotedPrice));

        vehicle.getDocument().setRegistrationCertificate(extractBooleanValueForColumn(cursor, VehicleColumns.registrationCertDoc));

        vehicle.getVehicleDate().setRegistrationDate(extractStringValueForColumn(cursor, VehicleColumns.registrationDate));

        vehicle.getDocument().setRoadMaintenance(extractBooleanValueForColumn(cursor, VehicleColumns.roadMaintenanceDoc));

        vehicle.getSpec().setSaleName(extractStringValueForColumn(cursor, VehicleColumns.saleName));
        vehicle.getSpec().setSeries(extractStringValueForColumn(cursor, VehicleColumns.series));

        vehicle.getStatus().setSold(extractBooleanValueForColumn(cursor, VehicleColumns.sold));

        vehicle.getSummary().setTradeTimes(extractIntegerValueForColumn(cursor, VehicleColumns.tradeTimes));

        vehicle.getDocument().setTransferTicket(extractBooleanValueForColumn(cursor, VehicleColumns.transferTicketDoc));

        vehicle.setVin(extractStringValueForColumn(cursor, VehicleColumns.vin));
        vehicle.getSpec().setYearGroup(extractStringValueForColumn(cursor, VehicleColumns.yearGroup));

        vehicle.setUpdateAt(extractStringValueForColumn(cursor, VehicleColumns.updateAt));
        vehicle.setCreateAt(extractStringValueForColumn(cursor, VehicleColumns.createAt));

        vehicle.getPrice().setQuotedPriceIncludeTransferFee(extractBooleanValueForColumn(cursor, VehicleColumns.quotedPriceIncludeTransferFee));
        vehicle.getSummary().setInterior(extractStringValueForColumn(cursor, VehicleColumns.interior));
        vehicle.getStatus().setReject(extractBooleanValueForColumn(cursor, VehicleColumns.reject));
        vehicle.getStatus().setRejectComment(extractStringValueForColumn(cursor, VehicleColumns.rejectComment));
        vehicle.getStatus().setRejectReason(extractStringValueForColumn(cursor, VehicleColumns.rejectReason));
        vehicle.setModifyFrom(extractStringValueForColumn(cursor, VehicleColumns.modifyFrom));
        vehicle.getDesc().setIndividualList(vehicle.getDesc().fromJson(extractStringValueForColumn(cursor, VehicleColumns.individualList)));
        vehicle.getDesc().setHighlightList(vehicle.getDesc().fromHighlightJson(extractStringValueForColumn(cursor, VehicleColumns.highlightList)));
        vehicle.getSummary().setDrivingLicensePicture(extractStringValueForColumn(cursor, VehicleColumns.drivingLicensePicture));
        vehicle.getVehicleDate().setManufactureDate(extractStringValueForColumn(cursor, VehicleColumns.manufactureDate));
        vehicle.getSummary().setConsignment(extractBooleanValueForColumn(cursor, VehicleColumns.consignment));
        return vehicle;
    }

//    CREATE TABLE vehicles (_id integer  PRIMARY KEY AUTOINCREMENT DEFAULT NULL,
//                           id text,merchantUserId text,galleryId text,gallery text,vin text,
//                           levelId text,brand text,yearGroup text,series text,saleName text,provinceCode text,
//                           cityCode text,districtCode text,detail text,lng text,lat text,licenseNumber text,
//                           color text,interior text,quotedPriceIncludeTransferFee integer,mileage integer,
//                           tradeTimes integer,purpose text,emissionStandard text,accident integer,maintenance integer,
//                           registrationDate text,inspectionDate text,commercialInsuranceExpireDate text,
//                           alcInsuranceExpireDate text,quotedPrice integer,bidPrice integer,offerPrice integer,
//                           offline integer,sold integer,draft integer,contacts text,briefDesc text,fullDesc text,
//                           propertyDoc integer,instructionsDoc integer,transferTicketDoc integer,
//                           roadMaintenanceDoc integer,carTaxDoc integer,maintenanceManualDoc integer,
//                           purchaseTaxDoc integer,registrationCertDoc integer,createAt text,updateAt text,
//                           reject integer DEFAULT 0,rejectReason TEXT,rejectComment TEXT,modifyFrom TEXT,
//                           individualList TEXT, "highlightList" TEXT, "drivingLicensePicture" TEXT,
//                           "manufactureDate" TEXT, "consignment" INTEGER)
    public interface VehicleColumns {
        String rowId = "_id";
        String id = "id";
        String merchantUserId = "merchantUserId";
        String merchantId = "merchantId";
        String galleryId = "galleryId";
        String gallery = "gallery";
        String vin = "vin";
        String levelId = "levelId";
        String brand = "brand";
        String yearGroup = "yearGroup";
        String saleName = "saleName";
        String provinceCode = "provinceCode";
        String cityCode = "cityCode";
        String districtCode = "districtCode";
        String detail = "detail";
        String lng = "lng";
        String lat = "lat";
        String licenseNumber = "licenseNumber";
        String color = "color";
        String mileage = "mileage";
        String tradeTimes = "tradeTimes";
        String purpose = "purpose";
        String emissionStandard = "emissionStandard";
        String accident = "accident";
        String maintenance = "maintenance";
        String registrationDate = "registrationDate";
        String commercialInsuranceExpireDate = "commercialInsuranceExpireDate";
        String alcInsuranceExpireDate = "alcInsuranceExpireDate";
        String inspectionDate = "inspectionDate";
        String quotedPrice = "quotedPrice";
        String bidPrice = "bidPrice";
        String offerPrice = "offerPrice";
        String offline = "offline";
        String sold = "sold";
        String draft = "draft";
        String contacts = "contacts";
        String briefDesc = "briefDesc";
        String fullDesc = "fullDesc";
        String propertyDoc = "propertyDoc";
        String instructionsDoc = "instructionsDoc";
        String transferTicketDoc = "transferTicketDoc";
        String roadMaintenanceDoc = "roadMaintenanceDoc";
        String carTaxDoc = "carTaxDoc";
        String maintenanceManualDoc = "maintenanceManualDoc";
        String purchaseTaxDoc = "purchaseTaxDoc";
        String registrationCertDoc = "registrationCertDoc";
        String series = "series";
        String createAt = "createAt";
        String updateAt = "updateAt";
        String interior = "interior";
        String quotedPriceIncludeTransferFee = "quotedPriceIncludeTransferFee";
        String reject = "reject";
        String rejectReason = "rejectReason";
        String rejectComment = "rejectComment";
        String modifyFrom = "modifyFrom";
        String individualList = "individualList";
        String highlightList = "highlightList";
        String drivingLicensePicture = "drivingLicensePicture";
        String manufactureDate = "manufactureDate";
        String consignment = "consignment";
        String[] _list = new String[]{rowId, brand, series, yearGroup, saleName, mileage, quotedPrice, gallery, registrationDate, createAt, updateAt, reject};

        String[] _all = new String[]{rowId, id, merchantUserId,merchantId, galleryId, gallery, vin, levelId, interior, quotedPriceIncludeTransferFee,
                brand, yearGroup, saleName, series, provinceCode, cityCode, districtCode, detail, lng, lat, licenseNumber,
                color, mileage, tradeTimes, purpose, emissionStandard, accident, maintenance, registrationDate,
                commercialInsuranceExpireDate, alcInsuranceExpireDate, inspectionDate, quotedPrice, bidPrice, offerPrice, offerPrice,
                offline, sold, draft, contacts, briefDesc, fullDesc, propertyDoc, instructionsDoc, transferTicketDoc,
                roadMaintenanceDoc, carTaxDoc, maintenanceManualDoc, purchaseTaxDoc, registrationCertDoc, createAt, updateAt, reject, rejectReason,
                rejectComment, modifyFrom, individualList, highlightList, drivingLicensePicture, manufactureDate, consignment};

    }
}
